Imports CAPA_Entidad
Imports CAPA_Utilitarios
Imports System.Data
Imports System.Data.SqlClient
Public Class DocumentosAlmacenDatos

    'Se crea una instancia unica para las conexiones
    Dim conex As Conexion = Conexion.getInstance()

    'Actualizamos las fechas de inicio de las tablas DADP y DADPU
    Function actualizarFechasInicio(ByVal codCIA As String, ByVal codSUC As String, ByVal tdoCod As String, ByVal codDCA As String, _
                                    ByVal codDAP As String, ByVal codDADP As String, ByVal codDADPU As String) As Boolean
        Dim valor As Boolean = False
        Dim query As String = ""
        Dim coneccion As SqlConnection = conex.obtenerConexion()

        Dim comando As New SqlCommand
        Dim transaction As SqlTransaction = Nothing
        Try
            coneccion.Open()
            transaction = coneccion.BeginTransaction

            query = "UPDATE Documento_Almacen_Detalle_Pickeador " & _
                    "SET DADP_FechaInicio = GETDATE() " & _
                    "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND TDOC_Codigo = @TDOC " & _
                    "AND DCA_Codigo = @DCA AND DAP_Codigo = @DAP AND DADP_Codigo = @DADP " & _
                    "AND DADP_Activo = '1' AND DADP_FechaInicio IS NULL"

            comando = New SqlCommand(query, coneccion)
            With comando
                .CommandText = query
                .Transaction = transaction
                .Parameters.AddWithValue("@CIA", codCIA)
                .Parameters.AddWithValue("@SUC", codSUC)
                .Parameters.AddWithValue("@TDOC", tdoCod)
                .Parameters.AddWithValue("@DCA", codDCA)
                .Parameters.AddWithValue("@DAP", codDAP)
                .Parameters.AddWithValue("@DADP", codDADP)
                .ExecuteNonQuery()
            End With

            query = "UPDATE Documento_Almacen_Detalle_Pickeador_Ubicacion " & _
                    "SET DADPU_FechaInicio = GETDATE() " & _
                    "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND TDOC_Codigo = @TDOC AND DCA_Codigo = @DCA " & _
                    "AND DADP_Codigo = @DADP AND DADPU_Codigo = @DADPU AND DADPU_Activo = '1' " & _
                    "AND DADPU_FechaInicio IS NULL"

            comando = New SqlCommand(query, coneccion)
            With comando
                .CommandText = query
                .Transaction = transaction
                .Parameters.AddWithValue("@CIA", codCIA)
                .Parameters.AddWithValue("@SUC", codSUC)
                .Parameters.AddWithValue("@TDOC", tdoCod)
                .Parameters.AddWithValue("@DCA", codDCA)
                .Parameters.AddWithValue("@DADP", codDADP)
                .Parameters.AddWithValue("@DADPU", codDADPU)
                .ExecuteNonQuery()
            End With

            transaction.Commit()
            valor = True
        Catch e As Exception
            transaction.Rollback()
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    Function actualizarCantidadesFechas(ByVal codCIA As String, ByVal codSUC As String, ByVal tdoCod As String, ByVal codDCA As String, _
                                    ByVal codDAP As String, ByVal codDADP As String, ByVal codDADPU As String, ByVal cantidad As Integer, ByVal flag As String) As Boolean
        Dim valor As Boolean = False
        Dim query As String = ""
        Dim coneccion As SqlConnection = conex.obtenerConexion()

        Dim comando As New SqlCommand
        Dim transaction As SqlTransaction = Nothing
        Try
            coneccion.Open()
            transaction = coneccion.BeginTransaction

            query = "UPDATE Documento_Almacen_Detalle_Pickeador_Ubicacion " & _
                    "SET" & IIf(flag = "0", " ", " DADPU_Cantidad = @CANT, ") & " DADPU_CantPick = DADPU_CantPick + @CANT, DADPU_FechaFin = GETDATE() " & _
                    "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND TDOC_Codigo = @TDO " & _
                    "AND DCA_Codigo = @DCA AND DADP_Codigo = @DADP AND DADPU_Codigo = @DADPU " & _
                    "AND DADPU_Activo = '1'"

            comando = New SqlCommand(query, coneccion)
            With comando
                .CommandText = query
                .Transaction = transaction
                .Parameters.AddWithValue("@CIA", codCIA)
                .Parameters.AddWithValue("@SUC", codSUC)
                .Parameters.AddWithValue("@TDO", tdoCod)
                .Parameters.AddWithValue("@DCA", codDCA)
                .Parameters.AddWithValue("@DADP", codDADP)
                .Parameters.AddWithValue("@DADPU", codDADPU)
                .Parameters.AddWithValue("@CANT", cantidad)
                .ExecuteNonQuery()
            End With

            'query = "UPDATE dadp " & _
            '        "SET dadp.DADP_CantPick = tabla1.Cantidad " & _
            '        "FROM Documento_Almacen_Detalle_Pickeador dadp " & _
            '        "INNER JOIN (SELECT dadp.CIA, dadp.SUC_Codigo, dadp.TDOC_Codigo, dadp.DCA_Codigo, dadp.DADP_Codigo, " & _
            '        "SUM(dadpu.DADPU_CantPick) AS Cantidad " & _
            '        "FROM Documento_Almacen_Detalle_Pickeador dadp " & _
            '        "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " & _
            '        "ON dadpu.CIA = dadp.CIA AND dadpu.SUC_Codigo = dadp.SUC_Codigo AND dadpu.TDOC_Codigo = dadp.TDOC_Codigo " & _
            '        "AND dadpu.DCA_Codigo = dadp.DCA_Codigo AND dadpu.DADP_Codigo = dadp.DADP_Codigo AND dadpu.DADPU_Activo = '1' " & _
            '        "GROUP BY dadp.CIA, dadp.SUC_Codigo, dadp.TDOC_Codigo, dadp.DCA_Codigo, dadp.DADP_Codigo, dadp.DADP_Cantidad) tabla1 " & _
            '        "ON tabla1.CIA = dadp.CIA AND tabla1.SUC_Codigo = dadp.SUC_Codigo AND tabla1.TDOC_Codigo = dadp.TDOC_Codigo " & _
            '        "AND tabla1.DCA_Codigo = dadp.DCA_Codigo AND tabla1.DADP_Codigo = dadp.DADP_Codigo AND dadp.DADP_Activo = '1' " & _
            '        "WHERE dadp.CIA = @CIA AND dadp.SUC_Codigo = @SUC AND dadp.TDOC_Codigo = @TDO " & _
            '        "AND dadp.DCA_Codigo = @DCA AND dadp.DAP_Codigo = @DAP AND dadp.DADP_Codigo = @DADP AND DADP_Activo = '1'"

            '', (CASE WHEN dadp.DADP_Cantidad - SUM(dadpu.DADPU_CantPick) = 0 THEN GETDATE() ELSE NULL END) AS Fecha

            'comando = New SqlCommand(query, coneccion)
            'With comando
            '    .CommandText = query
            '    .Transaction = transaction
            '    .Parameters.AddWithValue("@CIA", codCIA)
            '    .Parameters.AddWithValue("@SUC", codSUC)
            '    .Parameters.AddWithValue("@TDO", tdoCod)
            '    .Parameters.AddWithValue("@DCA", codDCA)
            '    .Parameters.AddWithValue("@DADP", codDADP)
            '    .Parameters.AddWithValue("@DAP", codDAP)
            '    .ExecuteNonQuery()
            'End With

            transaction.Commit()
            valor = True
        Catch e As Exception
            transaction.Rollback()
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

End Class
